package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;


import ModelLayer.Sale;

public class DBSale {
	private static Connection con;
	Statement stmt;
	public DBSale() 
	{
		con = DbConnection.getInstance().getDBcon();
	}
	public void createSale(String date,int quantity,String deliveryS,String deliveryD,int cID,int pID,int iID)
	{	String query="";
		try{
			int sID=GetMax.getMaxId("Select max(sID) from Sale")+1;//Gets the maximum sID from the DB and adds+1 for the next ID
			
			 query ="INSERT INTO Sale( sID, date, quantity, delivery_status, delivery_date, cID, pID, iID)  VALUES('"+sID+"','"+date+"','"+quantity+"','"+deliveryS+"','"+deliveryD+"','"+cID+"','"+pID+"','"+iID+"')";
			
			 Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
		}
		catch(Exception e)
		{	//System.out.println("SuID,clID or gID is most probably invalid!");
			e.printStackTrace();
			}
	}
	public void deleteSale(int sID)
	{	//sID=1; 
		try{
			String query="DELETE FROM Sale WHERE sID = '"+sID+"'";
			Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
		}
		catch(Exception e)
		{e.printStackTrace();
		}
	}
	public void updateSale(Sale g)
	{ Sale suObj = g;
	String query="UPDATE Sale SET "+
		 	  "date ='"+ suObj.getDate()+"', "+
		 	  "quantity ='"+ suObj.getQuantity() + "'" +
		 	 "delivery_status ='"+ suObj.getD_status() + "'" +
		 	"delivery_date ='"+ suObj.getD_date() + "'" +
		 	"cID ='"+ suObj.getcID() + "'" +
		 	"pID ='"+ suObj.getpID() + "'" +
		 	"iID ='"+ suObj.getiID() + "'" +
		 	      " WHERE sID = '"+ suObj.getsID() + "'";
              System.out.println("Update query:" + query);
              try{
            	  Statement stmt = con.createStatement();
            	  stmt.setQueryTimeout(5);
            	  stmt.executeUpdate(query);
            	  stmt.close();
            	  
              }
              catch(Exception e)
              {e.printStackTrace();}
		
	}
	
	private String buildQuery()
	{
	    String query="SELECT sID, date, quantity, delivery_status, delivery_date, cID, pID, iID  FROM Sale";
		/*
		if (wClause.length()>0)
			query=query+" WHERE ";
			*/
		return query;
	}
	public ArrayList<Sale> miscWhere()
	{
            ResultSet results;
	    ArrayList<Sale> list = new ArrayList<Sale>();	
		
	    String query =  buildQuery();
  
            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 Sale empObj = new Sale();
		 empObj = buildSale(results);	
                 list.add(empObj);	
		}//end while
                 stmt.close();     
                 
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	public Sale findSale(int sID)
	{
		ResultSet results;
		Sale empObj = new Sale();
                
	        String query =  buildQuery() + " WHERE sID ="+"'"+sID+"'";
                System.out.println(query);
		try{ // 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            empObj = buildSale(results);
                            //assocaition is to be build
                            stmt.close();
                            
                            
			}
                        else{ //no Sale was found
                            empObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return empObj;
	}
	private Sale buildSale(ResultSet results)
    {   Sale suObj = new Sale();
        try{ // the columns from the table glayee  are used
        	//sID, date, quantity, delivery_status, delivery_date, cID, pID, iID
              suObj.setsID(results.getInt("sID"));
              suObj.setDate(results.getString("date"));
              suObj.setQuantity(results.getInt("quantity"));
              suObj.setD_status(results.getString("delivery_status"));
              suObj.setD_date(results.getString("delivery_date"));
              suObj.setcID(results.getInt("cID"));
              suObj.setpID(results.getInt("pID"));
              suObj.setiID(results.getInt("iID"));
              
              
        }
       catch(Exception e)
       {
           System.out.println("Error in building the Sale object");
       }
       return suObj;
}
	
}